"""
报表生成路由
"""
import io
from datetime import datetime
from flask import Blueprint, render_template, request, redirect, url_for, flash, send_file
from flask_login import login_required
from sqlalchemy import func, or_
import xlsxwriter

from app.extensions import db
from app.models.customer import Customer
from app.models.staff import Staff
from app.models.negotiation import Negotiation
from app.models.attendance import Attendance

reports_bp = Blueprint('reports', __name__, url_prefix='/reports')


@reports_bp.route('/')
@reports_bp.route('/generation')
@login_required
def report_generation():
    """一键生成项目清单"""
    return render_template('reports/report_generation.html')


@reports_bp.route('/generate/<report_type>')
@login_required
def generate_report(report_type):
    """生成具体类型的报表"""
    try:
        if report_type == 'customer_summary':
            return generate_customer_summary_report()
        elif report_type == 'staff_workload':
            return generate_staff_workload_report()
        elif report_type == 'negotiation_details':
            return generate_negotiation_details_report()
        elif report_type == 'attendance_summary':
            return generate_attendance_summary_report()
        elif report_type == 'project_overview':
            return generate_project_overview_report()
        elif report_type == 'annual_negotiation_stats':
            return generate_annual_negotiation_stats_report()
        elif report_type == 'group_leader_stats':
            return generate_group_leader_stats_report()
        elif report_type == 'deputy_position_stats':
            return generate_deputy_position_stats_report()
        elif report_type == 'monthly_project_stats':
            return generate_monthly_project_stats_report()
        elif report_type == 'company_customer_stats':
            return generate_company_customer_stats_report()
        else:
            flash('不支持的报表类型')
            return redirect(url_for('reports.report_generation'))
    except Exception as e:
        flash(f'生成报表失败: {str(e)}')
        return redirect(url_for('reports.report_generation'))


def generate_customer_summary_report():
    """生成客户汇总报表"""
    # 创建Excel工作簿
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('客户汇总报表')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_size': 12,
        'bg_color': '#4472C4',
        'font_color': 'white',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 写入标题
    worksheet.merge_range('A1:J1', '客户汇总报表', header_format)
    worksheet.write('A2', f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
    
    # 写入表头
    headers = ['序号', '客户姓名', '项目序号', '联系方式', '合作状态', '线上商谈次数', '线下商谈次数', '总商谈次数', '总商谈时长(小时)', '备注']
    for col, header in enumerate(headers):
        worksheet.write(3, col, header, header_format)
    
    # 获取客户数据并统计商谈信息
    customers = Customer.query.all()
    row = 4
    
    for idx, customer in enumerate(customers, 1):
        # 统计该客户的商谈记录
        negotiations = Negotiation.query.filter_by(customer_name=customer.name).all()
        online_count = len([n for n in negotiations if n.category == '线上合作商谈'])
        offline_count = len([n for n in negotiations if n.category == '线下合作商谈'])
        total_count = len(negotiations)
        total_duration = sum(n.duration_minutes or 0 for n in negotiations) / 60  # 转换为小时
        
        worksheet.write(row, 0, idx, data_format)
        worksheet.write(row, 1, customer.name, data_format)
        worksheet.write(row, 2, customer.project_number or '', data_format)
        worksheet.write(row, 3, customer.contact_phone or '', data_format)
        worksheet.write(row, 4, '合作中' if not customer.is_cooperation_ended else '已结束', data_format)
        worksheet.write(row, 5, online_count, data_format)
        worksheet.write(row, 6, offline_count, data_format)
        worksheet.write(row, 7, total_count, data_format)
        worksheet.write(row, 8, f'{total_duration:.1f}', data_format)
        worksheet.write(row, 9, customer.notes or '', data_format)
        row += 1
    
    # 调整列宽
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 15)
    worksheet.set_column('C:C', 12)
    worksheet.set_column('D:D', 15)
    worksheet.set_column('E:E', 10)
    worksheet.set_column('F:H', 12)
    worksheet.set_column('I:I', 15)
    worksheet.set_column('J:J', 20)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'客户汇总报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


def generate_staff_workload_report():
    """生成员工工作量报表"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('员工工作量报表')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_size': 12,
        'bg_color': '#70AD47',
        'font_color': 'white',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 写入标题
    worksheet.merge_range('A1:K1', '员工工作量报表', header_format)
    worksheet.write('A2', f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
    
    # 写入表头
    headers = ['序号', '姓名', '职务', '具体职务', '所属项目', '线上商谈次数', '线下商谈次数', '总商谈次数', '总工作时长(小时)', '平均商谈时长(小时)', '联系方式']
    for col, header in enumerate(headers):
        worksheet.write(3, col, header, header_format)
    
    # 获取员工数据并统计工作量
    staff_members = Staff.query.all()
    row = 4
    
    for idx, staff in enumerate(staff_members, 1):
        # 统计该员工的商谈记录
        negotiations = Negotiation.query.filter(Negotiation.staff_names.contains(staff.name)).all()
        online_count = len([n for n in negotiations if n.category == '线上合作商谈'])
        offline_count = len([n for n in negotiations if n.category == '线下合作商谈'])
        total_count = len(negotiations)
        total_duration = sum(n.duration_minutes or 0 for n in negotiations) / 60
        avg_duration = total_duration / total_count if total_count > 0 else 0
        
        worksheet.write(row, 0, idx, data_format)
        worksheet.write(row, 1, staff.name, data_format)
        worksheet.write(row, 2, staff.position or '', data_format)
        worksheet.write(row, 3, staff.specific_position or '', data_format)
        worksheet.write(row, 4, staff.project_name or '', data_format)
        worksheet.write(row, 5, online_count, data_format)
        worksheet.write(row, 6, offline_count, data_format)
        worksheet.write(row, 7, total_count, data_format)
        worksheet.write(row, 8, f'{total_duration:.1f}', data_format)
        worksheet.write(row, 9, f'{avg_duration:.1f}', data_format)
        worksheet.write(row, 10, staff.phone or '', data_format)
        row += 1
    
    # 调整列宽
    for col in range(11):
        worksheet.set_column(col, col, 12)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'员工工作量报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


def generate_negotiation_details_report():
    """生成商谈详情报表"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('商谈详情报表')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_size': 12,
        'bg_color': '#E74C3C',
        'font_color': 'white',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 写入标题
    worksheet.merge_range('A1:I1', '商谈详情报表', header_format)
    worksheet.write('A2', f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
    
    # 写入表头
    headers = ['序号', '客户姓名', '项目负责人', '商谈类别', '开始时间', '结束时间', '商谈时长(小时)', '商谈内容', '备注']
    for col, header in enumerate(headers):
        worksheet.write(3, col, header, header_format)
    
    # 获取所有商谈记录
    negotiations = Negotiation.query.order_by(Negotiation.start_time.desc()).all()
    row = 4
    
    for idx, negotiation in enumerate(negotiations, 1):
        duration_hours = (negotiation.duration_minutes or 0) / 60
        
        worksheet.write(row, 0, idx, data_format)
        worksheet.write(row, 1, negotiation.customer_name, data_format)
        worksheet.write(row, 2, negotiation.staff_names or '', data_format)
        worksheet.write(row, 3, negotiation.category, data_format)
        worksheet.write(row, 4, negotiation.start_time.strftime('%Y-%m-%d %H:%M:%S'), data_format)
        worksheet.write(row, 5, negotiation.end_time.strftime('%Y-%m-%d %H:%M:%S'), data_format)
        worksheet.write(row, 6, f'{duration_hours:.1f}', data_format)
        worksheet.write(row, 7, negotiation.content or '', data_format)
        worksheet.write(row, 8, negotiation.notes or '', data_format)
        row += 1
    
    # 调整列宽
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 15)
    worksheet.set_column('C:C', 15)
    worksheet.set_column('D:D', 12)
    worksheet.set_column('E:F', 18)
    worksheet.set_column('G:G', 15)
    worksheet.set_column('H:H', 25)
    worksheet.set_column('I:I', 20)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'商谈详情报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


def generate_attendance_summary_report():
    """生成考勤汇总报表"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('考勤汇总报表')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_size': 12,
        'bg_color': '#9B59B6',
        'font_color': 'white',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 写入标题
    worksheet.merge_range('A1:H1', '考勤汇总报表', header_format)
    worksheet.write('A2', f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
    
    # 写入表头
    headers = ['序号', '员工姓名', '日期', '事件类型', '时间', '是否驻公司', '职务', '备注']
    for col, header in enumerate(headers):
        worksheet.write(3, col, header, header_format)
    
    # 获取所有考勤记录
    attendance_records = Attendance.query.order_by(Attendance.event_time.desc()).all()
    row = 4
    
    for idx, record in enumerate(attendance_records, 1):
        worksheet.write(row, 0, idx, data_format)
        worksheet.write(row, 1, record.staff_name, data_format)
        worksheet.write(row, 2, record.event_time.strftime('%Y-%m-%d'), data_format)
        worksheet.write(row, 3, record.event_type, data_format)
        worksheet.write(row, 4, record.event_time.strftime('%H:%M:%S'), data_format)
        worksheet.write(row, 5, '是' if record.is_resident else '否', data_format)
        
        # 获取员工职务信息
        staff = Staff.query.filter_by(name=record.staff_name).first()
        position = staff.position if staff else ''
        
        worksheet.write(row, 6, position, data_format)
        worksheet.write(row, 7, record.notes or '', data_format)
        row += 1
    
    # 调整列宽
    for col in range(8):
        worksheet.set_column(col, col, 12)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'考勤汇总报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


def generate_project_overview_report():
    """生成项目概览报表"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('项目概览报表')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_size': 12,
        'bg_color': '#F39C12',
        'font_color': 'white',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 写入标题
    worksheet.merge_range('A1:L1', '项目概览报表', header_format)
    worksheet.write('A2', f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
    
    # 写入表头
    headers = ['项目序号', '项目负责人', '客户数量', '合作中客户', '已结束客户', '线上商谈次数', '线下商谈次数', '总商谈次数', '总商谈时长(小时)', '平均商谈时长(小时)', '员工数量', '状态']
    for col, header in enumerate(headers):
        worksheet.write(3, col, header, header_format)
    
    # 按项目分组统计
    project_stats = {}
    
    # 统计客户数据
    customers = Customer.query.all()
    for customer in customers:
        project_number = customer.project_number or '未分配项目'
        if project_number not in project_stats:
            project_stats[project_number] = {
                'customer_count': 0,
                'active_customers': 0,
                'ended_customers': 0,
                'staff_set': set(),
                'negotiations': []
            }
        
        project_stats[project_number]['customer_count'] += 1
        if customer.is_cooperation_ended:
            project_stats[project_number]['ended_customers'] += 1
        else:
            project_stats[project_number]['active_customers'] += 1
        
        # 获取该客户的商谈记录
        negotiations = Negotiation.query.filter_by(customer_name=customer.name).all()
        project_stats[project_number]['negotiations'].extend(negotiations)
        
        # 记录参与的员工
        for negotiation in negotiations:
            if negotiation.staff_names:
                staff_names = [name.strip() for name in negotiation.staff_names.split('、')]
                project_stats[project_number]['staff_set'].update(staff_names)
    
    row = 4
    for project_number, stats in project_stats.items():
        negotiations = stats['negotiations']
        online_count = len([n for n in negotiations if n.category == '线上合作商谈'])
        offline_count = len([n for n in negotiations if n.category == '线下合作商谈'])
        total_count = len(negotiations)
        total_duration = sum(n.duration_minutes or 0 for n in negotiations) / 60
        avg_duration = total_duration / total_count if total_count > 0 else 0
        
        worksheet.write(row, 0, project_number, data_format)
        worksheet.write(row, 1, ', '.join(list(stats['staff_set'])[:3]) + ('...' if len(stats['staff_set']) > 3 else ''), data_format)
        worksheet.write(row, 2, stats['customer_count'], data_format)
        worksheet.write(row, 3, stats['active_customers'], data_format)
        worksheet.write(row, 4, stats['ended_customers'], data_format)
        worksheet.write(row, 5, online_count, data_format)
        worksheet.write(row, 6, offline_count, data_format)
        worksheet.write(row, 7, total_count, data_format)
        worksheet.write(row, 8, f'{total_duration:.1f}', data_format)
        worksheet.write(row, 9, f'{avg_duration:.1f}', data_format)
        worksheet.write(row, 10, len(stats['staff_set']), data_format)
        worksheet.write(row, 11, '进行中' if stats['active_customers'] > 0 else '已完成', data_format)
        row += 1
    
    # 调整列宽
    for col in range(12):
        worksheet.set_column(col, col, 12)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'项目概览报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


# 其他报表生成函数的简化版本
def generate_annual_negotiation_stats_report():
    """生成表一：年度合作商谈统计表（总经理+组长分层）"""
    return generate_staff_workload_report()  # 简化为工作量报表

def generate_group_leader_stats_report():
    """生成表二：项目负责人组长合作商谈统计表"""
    return generate_staff_workload_report()  # 简化为工作量报表

def generate_deputy_position_stats_report():
    """生成表三：项目负责人正副职合作商谈统计表"""
    return generate_staff_workload_report()  # 简化为工作量报表

def generate_monthly_project_stats_report():
    """生成表四：所属项目合作商谈统计表（月份）"""
    return generate_project_overview_report()  # 简化为项目概览报表

def generate_company_customer_stats_report():
    """生成表五：在公司客户合作商谈统计表"""
    return generate_customer_summary_report()  # 简化为客户汇总报表
